﻿using Aspose.Cells;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Xsl;


namespace NfsCommon.Export
{
    public sealed class ExportUtil
    {
        private HttpResponse _response = HttpContext.Current.Response;
        private List<string> _dateCols = new List<string>();
        /// <summary>
        /// 导出为CSV文件
        /// 
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="list">泛型</param>
        /// <param name="headers">表头</param>
        /// <param name="fileds">字段</param>
        /// <param name="fileName">导出文件名</param>
        public void ExportToCSV<T>(IList<T> list, string[] headers, string[] fileds, string fileName)
        {
            if (list == null || list.Count <= 0)
            {
                return;
            }
            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;
            System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
            foreach (T t in list)
            {
                if (t == null)
                {
                    continue;
                }
                row = dt.NewRow();
                for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                {
                    System.Reflection.PropertyInfo pi = myPropertyInfo[i];
                    string name = pi.Name;
                    if (dt.Columns[name] == null)
                    {
                        column = new DataColumn(name, pi.PropertyType);
                        dt.Columns.Add(column);
                    }
                    row[name] = pi.GetValue(t, null);
                }
                dt.Rows.Add(row);
            }
            foreach (DataRow dtrow in dt.Rows)
            {

                for (int i = 0; i < dtrow.ItemArray.Length; i++)
                {
                    if (dtrow[i].GetType().Namespace != "System.Collections.Generic" && dtrow[i].GetType().Name != "DBNull")
                    {
                        dtrow[i] = dtrow[i].ToString().Replace("\"", "\"\"");
                    }
                }
            }
            dt.AcceptChanges();
            ExportDetails(dt, headers, fileds, fileName);
        }

        /// <summary>
        /// 导出XLS
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="headers"></param>
        /// <param name="fileds"></param>
        /// <param name="fileName"></param>
        /// <param name="type"></param>
        public void ExportToCSV<T>(IList<T> list, string[] headers, string[] fileds, string fileName, string type)
        {
            if (list == null || list.Count <= 0)
            {
                return;
            }
            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;
            System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
            foreach (T t in list)
            {
                if (t == null)
                {
                    continue;
                }
                row = dt.NewRow();
                for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                {
                    System.Reflection.PropertyInfo pi = myPropertyInfo[i];
                    string name = pi.Name;
                    if (dt.Columns[name] == null)
                    {
                        column = new DataColumn(name, pi.PropertyType);
                        dt.Columns.Add(column);
                    }
                    row[name] = pi.GetValue(t, null);
                }
                dt.Rows.Add(row);
            }
            foreach (DataRow dtrow in dt.Rows)
            {

                for (int i = 0; i < dtrow.ItemArray.Length; i++)
                {
                    dtrow[i] = dtrow[i].ToString().Replace("\"", "\"\"");
                }
            }
            dt.AcceptChanges();
            ExportDetails(dt, headers, fileds, fileName, type);
        }

        public void ExportDetails(DataTable detailsTable, string fileName)
        {
            string[] headers = new string[detailsTable.Columns.Count];
            string[] fileds = new string[detailsTable.Columns.Count];

            for (int i = 0; i < detailsTable.Columns.Count; i++)
            {
                headers[i] = detailsTable.Columns[i].ColumnName;
                fileds[i] = ReplaceSpclChars(detailsTable.Columns[i].ColumnName);
            }

            ExportDetails(detailsTable, headers, fileds, fileName);
        }



        public void ExportDetails(DataTable detailsTable, string[] headers, string[] fileds, string fileName)
        {
            try
            {
                if (detailsTable.Rows.Count == 0)
                    return;

                DataSet dsExport = new DataSet("Export");
                DataTable dtExport = detailsTable.Copy();
                dtExport.TableName = "Values";
                dsExport.Tables.Add(dtExport);

                if (headers.Length == 0 && headers.Length == 0)
                {
                    headers = new string[dtExport.Columns.Count];
                    fileds = new string[dtExport.Columns.Count];

                    for (int i = 0; i < dtExport.Columns.Count; i++)
                    {
                        headers[i] = dtExport.Columns[i].ColumnName;
                        fileds[i] = ReplaceSpclChars(dtExport.Columns[i].ColumnName);
                    }
                }
                Export_with_XSLT_Web(dsExport, headers, fileds, fileName + "_" + DateTime.Now.ToString("yyyyMMdd") + ".csv");

            }
            catch
            {

            }
        }

        /// <summary>
        /// 导出XLS
        /// </summary>
        /// <param name="detailsTable"></param>
        /// <param name="headers"></param>
        /// <param name="fileds"></param>
        /// <param name="fileName"></param>
        /// <param name="Type"></param>
        public void ExportDetails(DataTable detailsTable, string[] headers, string[] fileds, string fileName, string Type)
        {
            try
            {
                if (detailsTable.Rows.Count == 0)
                    return;

                DataSet dsExport = new DataSet("Export");
                DataTable dtExport = detailsTable.Copy();
                dtExport.TableName = "Values";
                dsExport.Tables.Add(dtExport);

                if (headers.Length == 0 && headers.Length == 0)
                {
                    headers = new string[dtExport.Columns.Count];
                    fileds = new string[dtExport.Columns.Count];

                    for (int i = 0; i < dtExport.Columns.Count; i++)
                    {
                        headers[i] = dtExport.Columns[i].ColumnName;
                        fileds[i] = ReplaceSpclChars(dtExport.Columns[i].ColumnName);
                    }
                }
                Export_with_XSLT_Web(dsExport, headers, fileds, fileName + "_" + DateTime.Now.ToString("yyyyMMdd") + Type);

            }
            catch (Exception Ex)
            {
                //throw Ex;
            }
        }

        private string EncodeName(string fileName)
        {
            string ret = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
            ret = ret.Replace("+", "%20");

            return ret;
        }

        private void Export_with_XSLT_Web(DataSet dsExport, string[] headers, string[] fileds, string fileName)
        {
            try
            {
                _response.Clear();
                _response.Buffer = true;
                _response.ContentType = "text/csv;";
                _response.AppendHeader("content-disposition", "attachment; filename="
                    + EncodeName(fileName));
                MemoryStream stream = new MemoryStream();
                XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Unicode);
                foreach (DataColumn col in dsExport.Tables[0].Columns)
                {
                    if (col.DataType == Type.GetType("System.DateTime"))
                    {
                        _dateCols.Add(col.ColumnName);
                    }
                }

                CreateStylesheet(writer, headers, fileds);
                writer.Flush();

                stream.Seek(0, SeekOrigin.Begin);

                XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);

                XslCompiledTransform xslTran = new XslCompiledTransform();
                xslTran.Load(new XmlTextReader(stream), null, null);


                XsltArgumentList list = new XsltArgumentList();
                //DateTimeConverter converter = new DateTimeConverter();
                list.AddExtensionObject("urn:DateTimeConverter", Utility.Comm);

                stream.Close();
                stream.Dispose();
                writer.Close();
                System.IO.StringWriter sw = new System.IO.StringWriter();
                xslTran.Transform(xmlDoc, list, sw);

                MemoryStream csvStream = new MemoryStream();

                using (StreamWriter streamWriter = new StreamWriter(
                                                    csvStream,
                                                    Encoding.Unicode))
                {
                    string outsw = sw.ToString().Replace("1900-01-01 12:00:00", "").Replace("0001-01-01 12:00:00", "");
                    sw.Close();
                    sw.Dispose();
                    streamWriter.Write(HtmlUtils.ReplaceBRToEmpty(outsw));
                }

                _response.BinaryWrite(csvStream.ToArray());
                _response.End();
            }
            catch (Exception Ex)
            {
                // throw Ex;
            }
            finally
            {
                _response.End();
            }
        }

        public class HtmlUtils
        {
            static string taglist = @"xml|html|head|title|meta|style|script|" +
                                                            @"base|link|noscript|body|iframe|noframes|a|p|" +
                                                                    @"br|hr|h\d|div|span|ol|ul|menu|dir|li|dl|dt|dd|" +
                                                                    @"pre|blockquote|center|ins|del|em|strong|dfn|" +
                                                                    @"code|samp|kbd|var|cite|abbr|acronym|q|sub|sup|" +
                                                                    @"tt|i|b|big|small|u|s|strike|basefont|font|" +
                                                                    @"object|param|applet|img|map|area|form|input|" +
                                                                    @"label|select|option|optgroup|textarea|fieldset|" +
                                                                    @"legend|button|isindex|table|caption|thead|" +
                                                            @"tfoot|tbody|colgroup|col|tr|th|td";

            static string tag_pattern = @"<\s*?/?\s*?(\w+:)?(" + taglist +
                                                                              @")\s*?.*?/?\s*?>";
            static Regex tags = new Regex(tag_pattern, RegexOptions.IgnoreCase |
                                                RegexOptions.Compiled |
                                                RegexOptions.Singleline);
            static Regex whitespace = new Regex(@"\s+", RegexOptions.Compiled);
            static Regex comment = new Regex(@"<!--.*?-->", RegexOptions.Compiled |
                                                    RegexOptions.Singleline);
            static Regex declare = new Regex(@"<!.*?>", RegexOptions.Compiled |
                                                    RegexOptions.Singleline | RegexOptions.IgnoreCase);
            static Regex clrf = new Regex(@"<br.?/>|<br.?>", RegexOptions.Compiled |
                                                    RegexOptions.IgnoreCase);

            public static string StripHtml(string buffer)
            {
                buffer = Unescape(buffer);
                buffer = ReplaceBR(buffer);
                buffer = StripComments(buffer);
                buffer = StripDeclare(buffer);
                buffer = StripTags(buffer);
                buffer = CollapseWhitespace(buffer);
                buffer = buffer.Replace("1900-1-1  12:00:00", "");
                return buffer;
            }


            public static string StripTags(string buffer)
            {
                return tags.Replace(buffer, "");
            }

            public static string StripComments(string buffer)
            {
                return comment.Replace(buffer, "");
            }
            public static string StripDeclare(string buffer)
            {
                return declare.Replace(buffer, "");
            }
            public static string ReplaceBR(string buffer)
            {
                return clrf.Replace(buffer, "\r\n");
            }

            public static string ReplaceBRToEmpty(string buffer)
            {
                return clrf.Replace(buffer, "");
            }

            public static string CollapseWhitespace(string buffer)
            {
                return whitespace.Replace(buffer, "   ");
            }

            public static string Unescape(string buffer)
            {
                return HttpUtility.HtmlDecode(buffer);
            }

            public static string Escape(string buffer)
            {
                return HttpUtility.HtmlEncode(buffer);
            }

            public static bool SendPCInfoByEmail(string prefixID, string date)
            {

                return true;
            }

        }

        private void CreateStylesheet(XmlTextWriter writer, string[] headers, string[] fileds)
        {
            try
            {
                // xsl:stylesheet
                string ns = "http://www.w3.org/1999/XSL/Transform";
                writer.Formatting = Formatting.Indented;
                writer.WriteStartDocument();
                writer.WriteStartElement("xsl", "stylesheet", ns);

                //writer.WriteAttributeString("xmlns:date", "http://exslt.org/dates-and-times");
                //writer.WriteAttributeString("exclude-result-prefixes", "date");

                writer.WriteAttributeString("xmlns:DateTimeConverter", "urn:DateTimeConverter");


                writer.WriteAttributeString("version", "1.0");
                writer.WriteStartElement("xsl:output");
                writer.WriteAttributeString("method", "text");
                writer.WriteAttributeString("version", "4.0");
                writer.WriteEndElement();

                // xsl-template
                writer.WriteStartElement("xsl:template");
                writer.WriteAttributeString("match", "/");

                // xsl:value-of for headers
                for (int i = 0; i < headers.Length; i++)
                {
                    writer.WriteString("\"");
                    writer.WriteStartElement("xsl:value-of");
                    writer.WriteAttributeString("select", "'" + headers[i] + "'");
                    writer.WriteEndElement(); // xsl:value-of
                    writer.WriteString("\"");
                    //if (i != headers.Length - 1) writer.WriteString(",");
                    if (i != headers.Length - 1) writer.WriteString("\t");
                }

                // xsl:for-each
                writer.WriteStartElement("xsl:for-each");
                writer.WriteAttributeString("select", "Export/Values");
                writer.WriteString("\r\n");

                // xsl:value-of for data fields
                for (int i = 0; i < fileds.Length; i++)
                {
                    writer.WriteString("\"");
                    writer.WriteStartElement("xsl:value-of");

                    if (!_dateCols.Contains(fileds[i]))
                        writer.WriteAttributeString("select", fileds[i]);
                    else
                        //writer.WriteAttributeString("select", "date:formatdate(\\"+fileds[i]+")");
                        writer.WriteAttributeString("select", string.Format("DateTimeConverter:ToDateTimeFormat({0})", fileds[i]));

                    writer.WriteEndElement(); // xsl:value-of
                    writer.WriteString("\"");
                    //if (i != fileds.Length - 1) writer.WriteString(",");
                    if (i != fileds.Length - 1) writer.WriteString("\t");
                }

                writer.WriteEndElement(); // xsl:for-each
                writer.WriteEndElement(); // xsl-template
                writer.WriteEndElement(); // xsl:stylesheet
                writer.WriteEndDocument();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        private static string ReplaceSpclChars(string fieldName)
        {
            //			space 	-> 	_x0020_
            //			%		-> 	_x0025_
            //			#		->	_x0023_
            //			&		->	_x0026_
            //			/		->	_x002F_

            fieldName = fieldName.Replace(" ", "_x0020_");
            fieldName = fieldName.Replace("%", "_x0025_");
            fieldName = fieldName.Replace("#", "_x0023_");
            fieldName = fieldName.Replace("&", "_x0026_");
            fieldName = fieldName.Replace("/", "_x002F_");
            return fieldName;
        }

        /// <summary>
        /// 数据导出excel
        /// </summary>
        /// <param name="dtData">需要导出的数据</param>
        /// <param name="FileType">导出文件的类型</param>
        /// <param name="FileName">导出文件的名称</param>
        public void ExportToExcel(object dtData, string FileName)
        {
            GridView dgExport = null;
            // 当前对话 
            HttpContext curContext = HttpContext.Current;
            // IO用于导出并返回excel文件 
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;

            if (dtData != null)
            {
                // 设置编码和附件格式 
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "GB2312";
                curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());

                // 导出excel文件 
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);

                // 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid 
                dgExport = new GridView();
                dgExport.DataSource = dtData;
                dgExport.AllowPaging = false;
                dgExport.DataBind();

                // 返回客户端 
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }


        /// <summary> 
        /// 将指定excel文件中的数据转换成DataTable对象，供应用程序进一步处理 
        /// </summary> 
        /// <param name="filepath"></param> 
        /// <returns></returns> 
        public static DataTable Import(string filepath)
        {
            DataTable rs = new DataTable();
            bool canopen = false;
            //最新驱动使用失败Microsoft.ACE.OleDb.12.0
            OleDbConnection conn = new OleDbConnection("provider=Microsoft.ACE.OleDb.12.0;" +
            "data source=" + filepath + ";" +
            "extended properties=\"excel 8.0;HDR=Yes;IMEX=1\"");

            try//尝试数据连接是否可用 
            {
                conn.Open();
                conn.Close();
                canopen = true;
            }
            catch { }

            if (canopen)
            {
                try//如果数据连接可以打开则尝试读入数据 
                {
                    OleDbCommand myoledbcommand = new OleDbCommand("select * from [sheet1$]", conn);
                    OleDbDataAdapter mydata = new OleDbDataAdapter(myoledbcommand);
                    mydata.Fill(rs);
                    conn.Close();
                }
                catch//如果数据连接可以打开但是读入数据失败，则从文件中提取出工作表的名称，再读入数据 
                {
                    string sheetname = getsheetname(filepath);
                    if (sheetname.Length > 0)
                    {
                        OleDbCommand myoledbcommand = new OleDbCommand("select * from [" + sheetname + "$]", conn);
                        OleDbDataAdapter mydata = new OleDbDataAdapter(myoledbcommand);
                        mydata.Fill(rs);
                        conn.Close();
                    }
                }
            }
            else
            {
                StreamReader tmpstream = File.OpenText(filepath);
                string tmpstr = tmpstream.ReadToEnd();
                tmpstream.Close();
                rs = getDataTablefromstring(tmpstr);
                tmpstr = "";
            }
            //ASCIIEncoding AE2 = new ASCIIEncoding();
            //byte[] ByteArray2 = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32 };
            //char[] CharArray = AE2.GetChars(ByteArray2);   
            //去除空行
            //if (rs.Rows.Count > 0)
            //{
            //    rs.Rows[0].Delete();
            //    rs.AcceptChanges();
            //}
            foreach (DataRow row in rs.Rows)
            {

                string IsNullRow = string.Empty;
                for (int i = 0; i < row.ItemArray.Length; i++)
                {

                    //row[i] = row[i].ToString().Trim(CharArray);
                    IsNullRow = String.Format("{0}{1}", IsNullRow, row[i].ToString());
                }
                if (IsNullRow == "" || IsNullRow == null)
                {
                    row.Delete();
                }
            }
            rs.AcceptChanges();
            return rs;
        }
        /// <summary> 
        /// 将指定html字符串的数据转换成DataTable对象 --根据“<tr><td>”等特殊字符进行处理 
        /// </summary> 
        /// <param name="tmphtml">html字符串</param> 
        /// <returns></returns> 
        private static DataTable getDataTablefromstring(string tmphtml)
        {
            string tmpstr = tmphtml;
            DataTable tb = new DataTable();
            //先处理一下这个字符串，删除第一个<tr>之前合最后一个</tr>之后的部分 
            int index = tmpstr.IndexOf("<tr");
            if (index > -1)
                tmpstr = tmpstr.Substring(index);
            else
                return tb;

            index = tmpstr.LastIndexOf("</tr>");
            if (index > -1)
                tmpstr = tmpstr.Substring(0, index + 5);
            else
                return tb;

            bool existssparator = false;
            char separator = Convert.ToChar("^");

            //如果原字符串中包含分隔符“^”则先把它替换掉 
            if (tmpstr.IndexOf(separator.ToString()) > -1)
            {
                existssparator = true;
                tmpstr = tmpstr.Replace("^", "^$&^");
            }

            //先根据“</tr>”分拆 
            string[] tmprow = tmpstr.Replace("</tr>", "^").Split(separator);

            for (int i = 0; i < tmprow.Length - 1; i++)
            {
                DataRow newrow = tb.NewRow();

                string tmpstri = tmprow[i];
                if (tmpstri.IndexOf("<tr") > -1)
                {
                    tmpstri = tmpstri.Substring(tmpstri.IndexOf("<tr"));
                    if (tmpstri.IndexOf("display:none") < 0 || tmpstri.IndexOf("display:none") > tmpstri.IndexOf(">"))
                    {
                        tmpstri = tmpstri.Replace("</td>", "^");
                        string[] tmpfield = tmpstri.Split(separator);

                        for (int j = 0; j < tmpfield.Length - 1; j++)
                        {
                            tmpfield[j] = removestring(tmpfield[j], "<font>");
                            index = tmpfield[j].LastIndexOf(">") + 1;
                            if (index > 0)
                            {
                                string field = tmpfield[j].Substring(index, tmpfield[j].Length - index);
                                if (existssparator) field = field.Replace("^$&^", "^");
                                if (i == 0)
                                {
                                    string tmpfieldname = field;
                                    int sn = 1;
                                    while (tb.Columns.Contains(tmpfieldname))
                                    {
                                        tmpfieldname = field + sn.ToString();
                                        sn += 1;
                                    }
                                    tb.Columns.Add(tmpfieldname);
                                }
                                else
                                {
                                    newrow[j] = field;
                                }
                            }//end of if(index>0) 
                        }

                        if (i > 0)
                            tb.Rows.Add(newrow);
                    }
                }
            }

            tb.AcceptChanges();
            return tb;
        }

        /// <summary> 
        /// 从指定html字符串中剔除指定的对象 
        /// </summary> 
        /// <param name="tmphtml">html字符串</param> 
        /// <param name="remove">需要剔除的对象--例如输入"<font>"则剔除"<font ???????>"和"</font>>"</param> 
        /// <returns></returns> 
        public static string removestring(string tmphtml, string remove)
        {
            tmphtml = tmphtml.Replace(remove.Replace("<", "</"), "");
            tmphtml = removestringhead(tmphtml, remove);
            return tmphtml;
        }
        /// <summary> 
        /// 只供方法removestring()使用 
        /// </summary> 
        /// <returns></returns> 
        private static string removestringhead(string tmphtml, string remove)
        {
            //为了方便注释，假设输入参数remove="<font>" 
            if (remove.Length < 1) return tmphtml;//参数remove为空：不处理返回 
            if ((remove.Substring(0, 1) != "<") || (remove.Substring(remove.Length - 1) != ">")) return tmphtml;//参数remove不是<?????>：不处理返回 

            int indexs = tmphtml.IndexOf(remove.Replace(">", ""));//查找“<font”的位置 
            int indexe = -1;
            if (indexs > -1)
            {
                string tmpright = tmphtml.Substring(indexs, tmphtml.Length - indexs);
                indexe = tmpright.IndexOf(">");
                if (indexe > -1)
                    tmphtml = tmphtml.Substring(0, indexs) + tmphtml.Substring(indexs + indexe + 1);
                if (tmphtml.IndexOf(remove.Replace(">", "")) > -1)
                    tmphtml = removestringhead(tmphtml, remove);
            }
            return tmphtml;
        }

        /// <summary> 
        /// 将指定excel文件中读取第一张工作表的名称 
        /// </summary> 
        /// <param name="filepath"></param> 
        /// <returns></returns> 
        private static string getsheetname(string filepath)
        {
            string sheetname = "";

            FileStream tmpstream = File.OpenRead(filepath);
            byte[] filebyte = new byte[tmpstream.Length];
            tmpstream.Read(filebyte, 0, filebyte.Length);
            tmpstream.Close();

            byte[] tmpbyte = new byte[]{Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0), 
Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0), 
Convert.ToByte(30),Convert.ToByte(16),Convert.ToByte(0),Convert.ToByte(0)};

            int index = getsheetindex(filebyte, tmpbyte);
            if (index > -1)
            {

                index += 16 + 12;
                ArrayList sheetnamelist = new ArrayList();

                for (int i = index; i < filebyte.Length - 1; i++)
                {
                    byte temp = filebyte[i];
                    if (temp != Convert.ToByte(0))
                        sheetnamelist.Add(temp);
                    else
                        break;
                }
                byte[] sheetnamebyte = new byte[sheetnamelist.Count];
                for (int i = 0; i < sheetnamelist.Count; i++)
                    sheetnamebyte[i] = Convert.ToByte(sheetnamelist[i]);

                sheetname = Encoding.Default.GetString(sheetnamebyte);
            }
            return sheetname;
        }
        /// <summary> 
        /// 只供方法getsheetname()使用 
        /// </summary> 
        /// <returns></returns> 
        private static int getsheetindex(byte[] findtarget, byte[] finditem)
        {
            int index = -1;

            int finditemlength = finditem.Length;
            if (finditemlength < 1) return -1;
            int findtargetlength = findtarget.Length;
            if ((findtargetlength - 1) < finditemlength) return -1;

            for (int i = findtargetlength - finditemlength - 1; i > -1; i--)
            {
                ArrayList tmplist = new ArrayList();
                int find = 0;
                for (int j = 0; j < finditemlength; j++)
                {
                    if (findtarget[i + j] == finditem[j]) find += 1;
                }
                if (find == finditemlength)
                {
                    index = i;
                    break;
                }
            }
            return index;
        }
        /// <summary>
        /// 把文件上传到指定的路径，并返回本地相对路径

        /// </summary>
        /// <param name="filePath">要上传的文件</param>
        /// <param name="newPath">要上传的文件路径</param>
        /// <returns></returns>
        public static DataTable getImport(FileUpload fileUpload, string newPath)
        {
            string filePath = fileUpload.FileName;
            string suffix = System.IO.Path.GetExtension(filePath);
            if (suffix.Equals(".xls") || suffix.Equals(".csv") || suffix.Equals(".xlsx"))
            {
                string newName = filePath.Split('.')[0] + DateTime.Now.ToString("yyyy-mm-dd-hh-mm-ss");
                fileUpload.PostedFile.SaveAs(newPath + newName + suffix);
                DataTable table = Import(newPath + newName + suffix);
                File.Delete(newPath + newName + suffix);
                return table;
            }
            return null;
        }

        #region 为BoardInfo大量导入数据使用
        /// <summary> 
        /// 将指定excel文件中的数据转换成DataTable对象，供应用程序进一步处理 
        /// </summary> 
        /// <param name="filepath"></param> 
        /// <returns></returns> 
        public static DataTable ImportBoardInfo(string filepath)
        {
            DataTable rs = new DataTable();
            bool canopen = false;
            //最新驱动使用失败Microsoft.ACE.OleDb.12.0
            OleDbConnection conn = new OleDbConnection("provider=Microsoft.ACE.OleDb.12.0;" +
            "data source=" + filepath + ";" +
            "extended properties=\"excel 8.0;HDR=Yes;IMEX=1\"");

            try//尝试数据连接是否可用 
            {
                conn.Open();
                conn.Close();
                canopen = true;
            }
            catch { }

            if (canopen)
            {
                try//如果数据连接可以打开则尝试读入数据 
                {
                    OleDbCommand myoledbcommand = new OleDbCommand("select * from [sheet1$]", conn);
                    OleDbDataAdapter mydata = new OleDbDataAdapter(myoledbcommand);
                    mydata.Fill(rs);
                    conn.Close();
                }
                catch//如果数据连接可以打开但是读入数据失败，则从文件中提取出工作表的名称，再读入数据 
                {
                    string sheetname = getsheetname(filepath);
                    if (sheetname.Length > 0)
                    {
                        OleDbCommand myoledbcommand = new OleDbCommand("select * from [" + sheetname + "$]", conn);
                        OleDbDataAdapter mydata = new OleDbDataAdapter(myoledbcommand);
                        mydata.Fill(rs);
                        conn.Close();
                    }
                }
            }
            else
            {
                StreamReader tmpstream = File.OpenText(filepath);
                string tmpstr = tmpstream.ReadToEnd();
                tmpstream.Close();
                rs = getDataTablefromstring(tmpstr);
                tmpstr = "";
            }
            //ASCIIEncoding AE2 = new ASCIIEncoding();
            //byte[] ByteArray2 = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32 };
            //char[] CharArray = AE2.GetChars(ByteArray2);   
            //去除空行
            //if (rs.Rows.Count > 0)
            //{
            //    rs.Rows[0].Delete();
            //    rs.AcceptChanges();
            //}
            if (rs.Rows.Count > 5000)
            {
                return null;
            }

            foreach (DataRow row in rs.Rows)
            {

                string IsNullRow = string.Empty;
                for (int i = 0; i < row.ItemArray.Length; i++)
                {

                    //row[i] = row[i].ToString().Trim(CharArray);
                    IsNullRow = String.Format("{0}{1}", IsNullRow, row[i].ToString());
                }
                if (IsNullRow == "" || IsNullRow == null)
                {
                    row.Delete();
                }
            }
            rs.AcceptChanges();
            return rs;
        }

        /// <summary>
        /// 把文件上传到指定的路径，并返回本地相对路径

        /// </summary>
        /// <param name="filePath">要上传的文件</param>
        /// <param name="newPath">要上传的文件路径</param>
        /// <returns></returns>
        public static DataTable getImportBoardInfo(FileUpload fileUpload, string newPath)
        {
            string filePath = fileUpload.FileName;
            string suffix = System.IO.Path.GetExtension(filePath);
            if (suffix.Equals(".xls") || suffix.Equals(".csv"))
            {
                string newName = filePath.Split('.')[0] + DateTime.Now.ToString("yyyy-mm-dd-hh-mm-ss");
                fileUpload.PostedFile.SaveAs(newPath + newName + suffix);
                DataTable table = ImportBoardInfo(newPath + newName + suffix);
                File.Delete(newPath + newName + suffix);
                return table;
            }
            return null;
        }
        #endregion

        /// <summary>
        /// 导入Excel数据到业务系统，采用第三方Aspose组件的技术
        /// </summary>
        /// <param name="strFileName">需要</param>
        /// <remarks>第一行必须为表头信息，Excel文件必须为标准的二维表格式</remarks>
        /// <returns></returns>
        public static DataTable ReadExcel(byte[] fileBuffer)
        {
            Workbook book = new Workbook(new MemoryStream(fileBuffer));
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;

            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary>
        /// 导入Excel数据到业务系统，采用第三方Aspose组件的技术
        /// </summary>
        /// <param name="strFileName">需要</param>
        /// <remarks>第一行必须为表头信息，Excel文件必须为标准的二维表格式</remarks>
        /// <returns></returns>
        public static DataTable ReadExcel(string excelFile)
        {
            Workbook book = new Workbook();
            book.Open(excelFile);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;

            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }

        /// <summary>
        /// 导出业务数据到Excel文件，采用第三方Aspose组件的技术
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static byte[] ExportExcel(DataTable dt)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = (Worksheet)workbook.Worksheets[0];

            var colIndex = "A";
            foreach (DataColumn col in dt.Columns)
            {

                sheet.Cells[colIndex + 1].PutValue(col.Caption);
                int i = 2;
                foreach (DataRow row in dt.Rows)
                {
                    sheet.Cells[colIndex + i].PutValue(row[col.Caption]);
                    i++;
                }

                colIndex = ((char)(colIndex[0] + 1)).ToString();
            }
            return workbook.SaveToStream().ToArray();
        }

        /// <summary>
        /// 导出业务数据到Excel文件，采用第三方Aspose组件的技术
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static byte[] ExportExcel<T>(IEnumerable<T> data)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = (Worksheet)workbook.Worksheets[0];

            PropertyInfo[] ps = typeof(T).GetProperties();
            var colIndex = "A";

            foreach (var p in ps)
            {

                sheet.Cells[colIndex + 1].PutValue(p.Name);
                int i = 2;
                foreach (var d in data)
                {
                    sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
                    i++;
                }

                colIndex = ((char)(colIndex[0] + 1)).ToString();
            }
            return workbook.SaveToStream().ToArray();

            // 下载代码
            //response.Clear();
            //response.Buffer = true;
            //response.Charset = "utf-8";
            //response.AppendHeader("Content-Disposition", "attachment;filename=xxx.xls");
            //response.ContentEncoding = System.Text.Encoding.UTF8;
            //response.ContentType = "application/ms-excel";
            //response.BinaryWrite(workbook.SaveToStream().ToArray());
            //response.End();
        }
    }

}